import sys,os,datetime
import pandas as pd
import xlwings as xw

def sht_value(d, headers):
	last_dicts = []
	for r in d:
		if r == '\n' or r.isspace():
			continue
		elif 'connected.' in r:
			break
		else:
			line = r.strip('\n').strip('\t').split(',')
			cell = {}
			for idx, value in enumerate(line):
				cell[headers[idx]] = str(value.strip())
			last_dicts.append(cell)
	if len(last_dicts) == 0:
		last_dicts = None
	return last_dicts

def copy_from_db(ar_config, ar_txt, db_type, dm_batch):
	with open(ar_txt, encoding='utf-8') as f:
		data = f.readlines()
		cnt = 0
		for i in data:
			if i.isspace():
				cnt += 1
				continue
			line = i.strip('\n').split(',')
			line[0] = line[0].strip('\t')
			line[0] = line[0].strip()
			if line[0] == '---mgt count---':
				print(cnt)
				mgt_cnt_begin = cnt + 1
			if line[0] == '---txn count---':
				print(cnt)
				txn_cnt_begin = cnt + 1
			if line[0] == '---configuration count---':
				print(cnt)
				cfg_cnt_begin = cnt + 1
			if line[0] == '---customer count---':
				print(cnt)
				cust_cnt_begin = cnt + 1
			if line[0] == '---organization count---':
				print(cnt)
				org_cnt_begin = cnt + 1
			if line[0] == '---org operator count---':
				print(cnt)
				org_oper_cnt_begin = cnt + 1
			if line[0] == '---sp operator count---':
				print(cnt)
				sp_oper_begin = cnt + 1
			if line[0] == '---cust account banlance---':
				print(cnt)
				cust_acct_begin = cnt + 1
			if line[0] == '---org account banlance---':
				org_acct_begin = cnt + 1
				print(cnt)
			if line[0] == '---sp account banlance---':
				print(cnt)
				sp_acct_begin = cnt + 1
			if line[0] == '---new Account Balance---':
				print(cnt)
				new_acct_begin = cnt + 1
			if line[0] == '---new Account Balance-TOP1000---':
				print(cnt)
				new_acct_top50_begin = cnt + 1
			if line[0] == '---Transaction---':
				print(cnt)
				trans_begin = cnt + 1
			if line[0] == '---Transaction TOP1000---':
				print(cnt)
				trans_top50_begin = cnt + 1
			if 'Disconnected ' in line[0] and 'oracle' in ar_txt:
				trans_top50_end = cnt + 1
			if 'gauss' in ar_txt:
				trans_top50_end = len(data)
			cnt += 1
		sht1_mgt_cnt = data[mgt_cnt_begin:txn_cnt_begin - 1]
		sht2_txn_cnt = data[txn_cnt_begin:cfg_cnt_begin - 1]
		sht3_cfg_cnt = data[cfg_cnt_begin:cust_cnt_begin - 1]
		sht4_cust_cnt = data[cust_cnt_begin:org_cnt_begin - 1]
		sht5_org_cnt = data[org_cnt_begin:org_oper_cnt_begin - 1]
		sht6_org_oper_cnt = data[org_oper_cnt_begin:sp_oper_begin - 1]
		sht7_sp_oper_cnt = data[sp_oper_begin:cust_acct_begin - 1]
		sht8_cust_acct = data[cust_acct_begin:org_acct_begin - 1]
		sht8_org_acct = data[org_acct_begin:sp_acct_begin - 1]
		sht8_sp_acct = data[sp_acct_begin:new_acct_begin - 1]
		sht9_new_acct = data[new_acct_begin:new_acct_top50_begin - 1]
		sht10_new_acct_top50 = data[new_acct_top50_begin:trans_begin - 1]
		sht11_trans = data[trans_begin:trans_top50_begin - 1]
		sht12_trans_top50 = data[trans_top50_begin:] if db_type == 'gauss' and 'gauss' in ar_txt else data[trans_top50_begin:trans_top50_end - 1]

		app = xw.App(visible=False, add_book=False)
		app.display_alerts = False
		app.screen_updating = False
		wb = app.books.open(ar_config['excel_filename'])

		if int(ar_config['sht1_mgt_cnt']['generate']):
			sht1 = wb.sheets[ar_config['sht1_mgt_cnt']['sht_name']]
			sht1.range(ar_config['sht1_mgt_cnt'][str(db_type)]).options(index=False).value = pd.DataFrame(sht_value(sht1_mgt_cnt, ar_config['sht1_mgt_cnt']['headers']))

		if int(ar_config['sht2_txn_cnt']['generate']):
			sht2 = wb.sheets[ar_config['sht2_txn_cnt']['sht_name']]
			sht2.range(ar_config['sht2_txn_cnt'][str(db_type)]).options(index=False).value = pd.DataFrame(sht_value(sht2_txn_cnt, ar_config['sht2_txn_cnt']['headers']))

		if int(ar_config['sht3_cfg_cnt']['generate']):
			sht3 = wb.sheets[ar_config['sht3_cfg_cnt']['sht_name']]
			sht3.range(ar_config['sht3_cfg_cnt'][str(db_type)]).options(index=False).value = pd.DataFrame(sht_value(sht3_cfg_cnt, ar_config['sht3_cfg_cnt']['headers']))

		if int(ar_config['sht4_cust_cnt']['generate']):
			sht4 = wb.sheets[ar_config['sht4_cust_cnt']['sht_name']]
			sht4.range(ar_config['sht4_cust_cnt'][str(db_type)]).options(index=False).value = pd.DataFrame(sht_value(sht4_cust_cnt, ar_config['sht4_cust_cnt']['headers']))

		if int(ar_config['sht5_org_cnt']['generate']):
			sht5 = wb.sheets[ar_config['sht5_org_cnt']['sht_name']]
			sht5.range(ar_config['sht5_org_cnt'][str(db_type)]).options(index=False).value = pd.DataFrame(sht_value(sht5_org_cnt, ar_config['sht5_org_cnt']['headers']))

		if int(ar_config['sht6_org_oper_cnt']['generate']):
			sht6 = wb.sheets[ar_config['sht6_org_oper_cnt']['sht_name']]
			sht6.range(ar_config['sht6_org_oper_cnt'][str(db_type)]).options(index=False).value = pd.DataFrame(sht_value(sht6_org_oper_cnt, ar_config['sht6_org_oper_cnt']['headers']))
		
		if int(ar_config['sht7_sp_oper_cnt']['generate']):
			sht7 = wb.sheets[ar_config['sht7_sp_oper_cnt']['sht_name']]
			sht7.range(ar_config['sht7_sp_oper_cnt'][str(db_type)]).options(index=False).value = pd.DataFrame(sht_value(sht7_sp_oper_cnt, ar_config['sht7_sp_oper_cnt']['headers']))
		
		if int(ar_config['sht8_acct']['cust']['generate']):
			sht8 = wb.sheets[ar_config['sht8_acct']['sht_name']]
			sht8.range(ar_config['sht8_acct']['cust'][str(db_type)]).options(index=False).value = pd.DataFrame(sht_value(sht8_cust_acct, ar_config['sht8_acct']['headers']))

		if int(ar_config['sht8_acct']['org']['generate']):
			sht8 = wb.sheets[ar_config['sht8_acct']['sht_name']]
			sht8.range(ar_config['sht8_acct']['org'][str(db_type)]).options(index=False).value = pd.DataFrame(sht_value(sht8_org_acct, ar_config['sht8_acct']['headers']))

		if int(ar_config['sht8_acct']['sp']['generate']):
			sht8 = wb.sheets[ar_config['sht8_acct']['sht_name']]
			sht8.range(ar_config['sht8_acct']['sp'][str(db_type)]).options(index=False).value = pd.DataFrame(sht_value(sht8_sp_acct, ar_config['sht8_acct']['headers']))
		
		if int(ar_config['sht9_new_acct']['generate']):
			sht9 = wb.sheets[ar_config['sht9_new_acct']['sht_name']]
			sht9.range(ar_config['sht9_new_acct'][str(db_type)]).options(index=False).value = pd.DataFrame(sht_value(sht9_new_acct, ar_config['sht9_new_acct']['headers']))
		
		if int(ar_config['sht10_new_acct_top50']['generate']):
			sht10 = wb.sheets[ar_config['sht10_new_acct_top50']['sht_name']]
			sht10.range(ar_config['sht10_new_acct_top50'][str(db_type)]).options(index=False).value = pd.DataFrame(sht_value(sht10_new_acct_top50, ar_config['sht10_new_acct_top50']['headers']))
		
		if int(ar_config['sht11_trans']['generate']):
			sht11 = wb.sheets[ar_config['sht11_trans']['sht_name']]
			sht11.range(ar_config['sht11_trans'][str(db_type)]).options(index=False).value = pd.DataFrame(sht_value(sht11_trans, ar_config['sht11_trans']['headers']))

		if int(ar_config['sht12_trans_top50']['generate']):
			sht12 = wb.sheets[ar_config['sht12_trans_top50']['sht_name']]
			sht12.range(ar_config['sht12_trans_top50'][str(db_type)]).options(index=False).value = pd.DataFrame(sht_value(sht12_trans_top50, ar_config['sht12_trans_top50']['headers']))
		
		ct = datetime.datetime.now()
		timestamp_str = ct.strftime('%Y-%m-%d_%H-%M-%S')
		
		out_excel = f"{ar_config['excel_filename'].split('.')[0]}_{dm_batch}_{timestamp_str}.{ar_config['excel_filename'].split('.')[-1]}"
		print(f"out_excel : {out_excel}")
		wb.save(out_excel)
		wb.close()
		app.quit()
	return out_excel

def main():
	dm_batch = 'dryrun1'
	
	#oracle_ar_txt = sys.argv[1]
	#gauss_ar_txt = sys.argv[2]
	
	gauss_ar_txt = 'evc_ar_gauss_0813.txt'
	oracle_ar_txt = 'evc_ar_oracle_0813.txt'
	
	ar_config = {
		'excel_filename': 'Audit_Report_model.xlsx',
		'sht1_mgt_cnt': {
			'sht_name': 'MGT Count',
			'headers': ['Table Name','Counts of Data'],
			'oracle': 'c11',
			'gauss': 'e11',
			'generate': 1
		},
		'sht2_txn_cnt': {
			'sht_name': 'TXN Count',
			'headers': ['Table Name','Counts of Data'],
			'oracle': 'c11',
			'gauss': 'e11',
			'generate': 1
		},
		'sht3_cfg_cnt': {
			'sht_name': 'Configuration Count',
			'headers': ['configuration','Counts of Data'],
			'oracle': 'c11',
			'gauss': 'e11',
			'generate': 1
		},
		'sht4_cust_cnt': {
			'sht_name': 'Customer Count',
			'headers': ['product','Counts of Data'],
			'oracle': 'c11',
			'gauss': 'e11',
			'generate': 0
		},
		'sht5_org_cnt': {
			'sht_name': 'Organization Count',
			'headers': ['product','Counts of Data'],
			'oracle': 'c11',
			'gauss': 'e11',
			'generate': 1
		},
		'sht6_org_oper_cnt': {
			'sht_name': 'Org Operator Count',
			'headers': ['Role','Counts of Data'],
			'oracle': 'c11',
			'gauss': 'e11',
			'generate': 1
		},
		'sht7_sp_oper_cnt': {
			'sht_name': 'SP Operator Count',
			'headers': ['Role','Counts of Data'],
			'oracle': 'c11',
			'gauss': 'e11',
			'generate': 1
		},
		'sht8_acct': {
			'sht_name': 'Account Balance',
			'headers': ['Account Types','Counts of Balance','Counts of Reserved_Balance','Counts of Unclear_Balance'],
			'cust': {
				'oracle': 'c12',
				'gauss': 'g12',
				'generate': 0
			},
			'org': {
				'oracle': 'c12',
				'gauss': 'g12',
				'generate': 1
			},
			'sp': {
				'oracle': 'c23',
				'gauss': 'g23',
				'generate': 0
			}
		},
		'sht9_new_acct': {
			'sht_name': 'New Account Balance',
			'headers': ['Account Types','Counts of Identity','Counts of Balance','Counts of Reserved_Balance','Counts of Unclear_Balance'],
			'oracle': 'c12',
			'gauss': 'h12',
			'generate': 1
		},
		'sht10_new_acct_top50': {
			'sht_name': 'New Account Balance TOP1000',
			'headers': ['Org Short Code','Account Types','Counts of Identity','Counts of Balance','Counts of Reserved_Balance','Counts of Unclear_Balance'],
			'oracle': 'c12',
			'gauss': 'i12',
			'generate': 1
		},
		'sht11_trans': {
			'sht_name': 'Transaction',
			'headers': ['org type','date','Counts of Credit','Credit Amount','Counts of Debit','Debit Amount'],
			'oracle': 'b11',
			'gauss': 'h11',
			'generate': 1
		},
		'sht12_trans_top50': {
			'sht_name': 'Transaction TOP1000',
			'headers': ['org short code','org type','date','Counts of Credit','Credit Amount','Counts of Debit','Debit Amount'],
			'oracle': 'b11',
			'gauss': 'i11',
			'generate': 1
		}
	}

	ar_config['excel_filename'] = copy_from_db(ar_config, gauss_ar_txt, 'gauss', dm_batch)
	copy_from_db(ar_config, oracle_ar_txt, 'oracle', dm_batch)

if __name__ == '__main__':
	time1 = datetime.datetime.now()
	main()
	time2 = datetime.datetime.now()

	print('start time: ', time1)
	print('end time: ', time2)
	print('total time: ', (time2 - time1))